﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using SAS.Core.Result;

namespace SAS.DataAccess
{
    internal static class DB
    {
        //Indentify selected connection name
        private static readonly string ConnectionStringName = ConfigurationManager.AppSettings.Get("ConnectionStringName");

        /// <summary>
        /// Selected SQL connection string
        /// </summary>
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;

        /// <summary>
        /// SQL connection time out from configuration.
        /// </summary>
        private static int TimeOut
        {
            get
            {
                string strCommandTimeOut = ConfigurationManager.AppSettings.Get("CommandTimeOut");
                int commandTimeout;
                if (Int32.TryParse(strCommandTimeOut, out commandTimeout))
                {
                    return commandTimeout;
                }
                
                //return default timeout connection
                return 60;
            }
        }

        /// <summary>
        /// Read a list of row and parse row data to objects
        /// </summary>
        /// <typeparam name="T">The type of objects that returned by execute the query</typeparam>
        /// <param name="storeName">Store procedure name</param>
        /// <param name="make">Structure that identifies the parsing from IDataReader to object</param>
        /// <param name="paramters">SQL parameter</param>
        /// <returns></returns>
        public static SASResult<IList<T>> ReadList<T>(string storeName, Func<IDataReader, T> make = null, Parameter[] paramters = null)
        {
            SASResult<IList<T>> returnResult = new SASResult<IList<T>>();

            //create default return result.
            IList<T> listResult = new List<T>();

            using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
            {
                //Initialize SQL command
                SqlCommand sqlCommand = new SqlCommand(storeName)
                                            {
                                                CommandType = CommandType.StoredProcedure,
                                                Connection = sqlConnection,
                                                CommandTimeout = TimeOut
                                            };

                //Add SQL parameters to command
                if (paramters != null && paramters.Length != 0)
                {
                    AddParameter(sqlCommand, paramters);
                }


                //Execute SQL command query
                try
                {
                    //Open SQL connection
                    sqlConnection.Open();

                    //execute sqlcommand to get the results from sql query in database
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

                    //Parse SQL data reader to list of objects result
                    if (make != null)
                    {
                        while (sqlDataReader.Read())
                        {
                            listResult.Add(make(sqlDataReader));
                        }    
                    }

                    //read SQL parameter output values
                    if (paramters != null)
                    {
                        foreach (var parameter in paramters)
                        {
                            if (parameter.ParameterDirection == ParameterDirection.Output)
                            {
                                var parameterReturnValue = Convert.ToString(sqlCommand.Parameters[parameter.Name].Value);
                                returnResult.OutputResult.Add(parameter.OutputReturnName, parameterReturnValue);
                            }
                        }
                    }

                    //command execute successfully.
                    returnResult.Result = listResult;
                    returnResult.IsSucessful = true;
                }
                catch (Exception exception)
                {
                    //TODO: DangTH(12/04/2012) need to implement logging function

                    //update return information
                    returnResult.IsSucessful = false;
                    returnResult.Message = exception.Message;
                }
                finally
                {
                    if(sqlConnection.State == ConnectionState.Open)
                        sqlConnection.Close();
                }
            }

            return returnResult;
        }

        /// <summary>
        /// Read the specific value for a row from database.
        /// </summary>
        /// <typeparam name="T">The type of objects that returned by execute the query</typeparam>
        /// <param name="storeName">Store procedure name</param>
        /// <param name="make">Structure that identifies the parsing from IDataReader to object</param>
        /// <param name="paramters">SQL parameter</param>
        /// <returns></returns>
        public static SASResult<T> Read<T>(string storeName, Func<IDataReader, T> make = null, Parameter[] paramters = null) where T : class
        {
            SASResult<T> returnResult = new SASResult<T>();

            //create default return result.
            T obj = default(T);

            using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
            {
                //Initialize SQL command
                SqlCommand sqlCommand = new SqlCommand(storeName)
                                            {
                                                CommandType = CommandType.StoredProcedure,
                                                Connection = sqlConnection,
                                                CommandTimeout = TimeOut
                                            };

                //Add SQL parameters to command
                if (paramters != null && paramters.Length != 0)
                {
                    AddParameter(sqlCommand, paramters);
                }


                //Execute SQL command query
                try
                {
                    //Open SQL connection
                    sqlConnection.Open();

                    //execute sqlcommand to get the result from SQL query in database
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

                    //Parse SQL data reader to object result
                    if (make != null)
                    {
                        if (sqlDataReader.Read())
                        {
                            obj = make(sqlDataReader);
                        }   
                    }

                    //read SQL parameter output values
                    if (paramters != null)
                    {
                        foreach (var parameter in paramters)
                        {
                            if (parameter.ParameterDirection == ParameterDirection.Output)
                            {
                                var parameterReturnValue = Convert.ToString(sqlCommand.Parameters[parameter.Name].Value);
                                returnResult.OutputResult.Add(parameter.OutputReturnName, parameterReturnValue);
                            }
                        }    
                    }

                    //command execute successfully.
                    returnResult.IsSucessful = true;
                    returnResult.Result = obj;

                }
                catch (Exception exception)
                {
                    //TODO: DangTH(12/04/2012) need to implement logging function

                    //update return information
                    returnResult.IsSucessful = false;
                    returnResult.Message = exception.Message;
                }
                finally
                {
                    if (sqlConnection.State == ConnectionState.Open)
                        sqlConnection.Close();
                }
            }

            return returnResult;
        }

        /// <summary>
        /// Execute and get the first value at first column and first row
        /// </summary>
        /// <typeparam name="T">The type of objects that returned by execute the query</typeparam>
        /// <param name="storeName">Store procedure name</param>
        /// <param name="paramters">SQL parameter</param>
        /// <returns></returns>
        public static SASResult<T> ExecuteScalar<T>(string storeName, Parameter[] paramters = null) where T : class
        {
            SASResult<T> returnResult = new SASResult<T>();

            //create default return result.
            T obj = default(T);

            using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
            {
                //Initialize SQL command
                SqlCommand sqlCommand = new SqlCommand(storeName)
                                            {
                                                CommandType = CommandType.StoredProcedure,
                                                Connection = sqlConnection,
                                                CommandTimeout = TimeOut
                                            };

                //Add SQL parameters to command
                if (paramters != null && paramters.Length != 0)
                {
                    AddParameter(sqlCommand, paramters);
                }

                //Execute SQL command query
                try
                {
                    //Open SQL connection
                    sqlConnection.Open();

                    //execute sqlcommand to get the result from SQL query in database
                    object objectReturn = sqlCommand.ExecuteScalar();

                    //command execute successfully.
                    returnResult.IsSucessful = true;

                    if (objectReturn != null)
                    {
                        obj = objectReturn as T;

                        //The data type is inputted not match with return type
                        if (obj == null)
                        {
                            returnResult.IsSucessful = false;
                            returnResult.Message = "Can not get the result from executing the query. The data type is inputted not match with return type";
                        }
                        else
                        {
                            returnResult.Result = obj;
                        }
                    }

                    //Read SQL parameters output values if they return values that can be read (Output parameters)
                    if (paramters != null && returnResult.IsSucessful)
                    {
                        foreach (var parameter in paramters)
                        {
                            if (parameter.ParameterDirection == ParameterDirection.Output)
                            {
                                var parameterReturnValue = Convert.ToString(sqlCommand.Parameters[parameter.Name].Value);
                                returnResult.OutputResult.Add(parameter.OutputReturnName, parameterReturnValue);
                            }
                        }
                    }
                }
                catch (Exception exception)
                {
                    //TODO: DangTH(12/04/2012) need to implement logging function

                    //update return information
                    returnResult.IsSucessful = false;
                    returnResult.Message = exception.Message;
                }
                finally
                {
                    if (sqlConnection.State == ConnectionState.Open)
                        sqlConnection.Close();
                }
            }

            return returnResult;
        }

        /// <summary>
        /// Add parameters to command
        /// </summary>
        /// <param name="sqlCommand">Specific SqlCommand</param>
        /// <param name="paramters">SQL parameters</param>
        private static void AddParameter(SqlCommand sqlCommand, IEnumerable<Parameter> paramters)
        {
            //Validate method's parameters
            if(sqlCommand == null || paramters == null) 
                return;

            foreach (var parameter in paramters)
            {
                //create SQL parameter
                SqlParameter sqlParameter = sqlCommand.CreateParameter();

                //assign sqlParameter name
                sqlParameter.ParameterName = parameter.Name;

                //assign sqlParameter value
                sqlParameter.Value = parameter.Value;

                if (parameter.Size.HasValue)
                {
                    sqlParameter.Size = parameter.Size.Value;
                }

                //assign sqlParameter direction value
                sqlParameter.Direction = parameter.ParameterDirection;

                if (parameter.DbType != SqlDbType.NVarChar)
                {
                    sqlParameter.SqlDbType = parameter.DbType;
                }

                //assign back sqlParameter to sqlCommand
                sqlCommand.Parameters.Add(sqlParameter);
            }

        }
    }
}